<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Reading data with SqliteDataReader</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="C#, SQLite, ADO.NET, SQLiteDataReader, SqliteCommand, SqlConnection">
<meta name="description" content="In this chapter of the SQLite C# tutorial, we read
data from the database using the SQLiteDataReader class.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Retrieving data with SqliteDataReader</h1>

<p>
The <b>SqliteDataReader</b> is a class used
to retrieve data from the database.  It is used with the
<b>SqliteCommand</b> class to execute an SQL SELECT statement
and then access the returned rows. It provides fast, forward-only, 
read-only access to query results. It is the most efficient way
to retrieve data from tables. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Rather than using a constructor, we create an instance of the
SqliteDataReader by calling the ExecuteReader() method of the 
SqliteCommand object. While the SqlDataReader is being used, 
the associated SqlConnection serves the SqlDataReader. No other 
operations can be performed on the SqlConnection  other than 
closing it. 
</p>


<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";

        using(SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            string stm = "SELECT * FROM Cars LIMIT 5";

            using (SqliteCommand cmd = new SqliteCommand(stm, con))
            {
                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read()) 
                    {
                        Console.WriteLine(rdr.GetInt32(0) + " " 
                            + rdr.GetString(1) + " " + rdr.GetInt32(2));
                    }         
                }
            }

            con.Close();   
         }
    }
}
</pre>

<p>
We get 5 cars from the Cars table and print them to 
the console. 
</p>

<pre class="explanation">
using (SqliteDataReader rdr = cmd.ExecuteReader())
</pre>

<p>
To create an <code>SQLiteDataReader</code> object, we must call 
the <code>ExecuteReader()</code> method of the 
<code>SqliteCommand</code> object.
</p>

<pre class="explanation">
while (rdr.Read()) 
{
    Console.WriteLine(rdr.GetInt32(0) + " " 
        + rdr.GetString(1) + " " + rdr.GetInt32(2));
}   
</pre>

<p>
The <code>Read()</code> method advances the data reader to the next record.
It returns true if there are more rows; otherwise false. We can retrieve the value using 
the array index notation, or use a specific method to access column values in their 
native data types. The latter is more efficient. 
</p>

<pre>
$ mono retrieve.exe 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
The first five rows of the Cars table.
</p>

<hr class="btm">

<p>
We can retrieve the fields by their column names. 
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";

        using(SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            string stm = "SELECT * FROM Cars LIMIT 5";

            using (SqliteCommand cmd = new SqliteCommand(stm, con))
            {
                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read()) 
                    {
                        Console.Write("{0} ", rdr["Id"]);
                        Console.Write("{0} ", rdr["Name"]);
                        Console.Write("{0} \n", rdr["Price"]);
                    }
                }         
            }

            con.Close();
        }
    }
}
</pre>

<p>
The example prints 5 rows from the Cars table. This time we use 
the column names to get the table fields. 
</p>

<pre class="explanation">
while (rdr.Read()) 
{
    Console.Write("{0} ", rdr["Id"]);
    Console.Write("{0} ", rdr["Name"]);
    Console.Write("{0} \n", rdr["Price"]);
}
</pre>

<p>
The database table fields are referenced by their column
names. 
</p>


<h2>Multiple statements</h2>

<p>
The ADO.NET specification allows to execute multiple
statements in a single string. In case of queries, the
<code>SqliteDataReader</code> returns multiple result sets.
It has the <code>NextResult()</code> method to navigate 
throught the result sets. 
</p>


<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "Data Source=:memory:";

        using (SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            string stm = "SELECT 25; SELECT 44; SELECT 33";

            using (SqliteCommand cmd = new SqliteCommand(stm, con))
            {
                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    do
                    {
                        rdr.Read();
                        Console.WriteLine("{0}", rdr.GetInt32(0));

                    } while (rdr.NextResult());

                }                
            }

            con.Close();
        }
    }
}
</pre>

<p>
We have three queries in one SQL string. There will be three result
sets. 
</p>

<pre class="explanation">
string stm = "SELECT 25; SELECT 44; SELECT 33";
</pre>

<p>
There are three SELECT statements. They are separated by the semicolon
character. Each of them will return a single value.
</p>

<pre class="explanation">
do
{
    rdr.Read();
    Console.WriteLine("{0}", rdr.GetInt32(0));

} while (rdr.NextResult());
</pre>

<p>
The <code>Read()</code> method advances the <code>SqliteDataReader</code> 
to the next record. The <code>GetInt32()</code> method retrieves the
value as a 32-bit signed integer. The <code>NextResult()</code> 
advances the data reader to the next result.
</p>

<pre>
$ dmcs multiple.cs -r:Mono.Data.Sqlite.dll 
$ mono multiple.exe 
25
44
33
</pre>

<p>
Running the example.
</p>

<p>
We have finished reading data with the SqliteDataReader.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 16, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

